import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;
import javax.swing.JButton;

import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;

import javax.swing.WindowConstants;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import javax.swing.SwingUtilities;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;


/**
* This code was edited or generated using CloudGarden's Jigloo
* SWT/Swing GUI Builder, which is free for non-commercial
* use. If Jigloo is being used commercially (ie, by a corporation,
* company or business for any purpose whatever) then you
* should purchase a license for each developer using Jigloo.
* Please visit www.cloudgarden.com for details.
* Use of Jigloo implies acceptance of these licensing terms.
* A COMMERCIAL LICENSE HAS NOT BEEN PURCHASED FOR
* THIS MACHINE, SO JIGLOO OR THIS CODE CANNOT BE USED
* LEGALLY FOR ANY CORPORATE OR COMMERCIAL PURPOSE.
*/
public class ProductsandSuppliers extends javax.swing.JFrame {
	private JScrollPane jScrollPane1;
	private JButton jButton1;
	private JTextField jTextField1;
	//private JTable jTable1;
	
	//private ResultSet trset;
	private int rowCount;
	//private int columnCount;
	//private ArrayList data = new ArrayList();
	Vector columnNames = new Vector();
	Vector data = new Vector();
	
	//public JTable thadstable;
	private JTable jTable1;
	//public DefaultTableModel thadstablemodel = new DefaultTableModel();
	private Connection thadsconn;
	private Statement thadsstmt;
	private ResultSet thadsresult;

	/**
	* Auto-generated main method to display this JFrame
	*/
	public static void main(String[] args) {
		SwingUtilities.invokeLater(new Runnable() {
			public void run() {
				ProductsandSuppliers inst = new ProductsandSuppliers();
				inst.setLocationRelativeTo(null);
				inst.setVisible(true);
			}
		});
	}
	
	public ProductsandSuppliers() {
		super();
		thadsConnect();
		initGUI();
		//thadsAgent();
		//disableAllFields();
		setDefaultCloseOperation(DISPOSE_ON_CLOSE);
	}
	
	private void initGUI() {
		try {
			setDefaultCloseOperation(WindowConstants.DISPOSE_ON_CLOSE);
			getContentPane().setLayout(null);
			{
				jScrollPane1 = new JScrollPane();
				getContentPane().add(jScrollPane1);
				jScrollPane1.setBounds(12, 18, 775, 229);
				{
					thadsAgent();
					
					TableModel jTable1Model = 
						new DefaultTableModel(data, columnNames);
					jTable1 = new JTable();
					jScrollPane1.setViewportView(jTable1);
					jTable1.setModel(jTable1Model);
					jTable1.setPreferredSize(new java.awt.Dimension(772, 179));
				}
			}
			{
				jTextField1 = new JTextField();
				getContentPane().add(jTextField1);
				jTextField1.setText("jTextField1");
				jTextField1.setBounds(12, 259, 72, 21);
			}
			{
				jButton1 = new JButton();
				getContentPane().add(jButton1);
				jButton1.setText("Show me!");
				jButton1.setBounds(329, 265, 134, 21);
				jButton1.addMouseListener(new MouseAdapter()
				{
					public void mouseClicked(MouseEvent e)
					{
						//disableAllFields();
						//thadsConnect();
						//thadsAgent();
						showData();
						//thadsCommit();
					}
				});
				
			}
			{
				
				//thadstable = new JTable(data, columnNames);
				//thadstable.setModel(thadstablemodel);
				//thadstable.setBounds(13, 34, 772, 203);
				//thadstable.setVisible(true);
			}
			pack();
			this.setSize(807, 340);
		} catch (Exception e) {
		    //add your error handling code here
			e.printStackTrace();
		}
	}
	
	//thads methods for data access
	public void thadsConnect()
	{
		try
		{
			//Class.forName("com.mysql.jdbc.Driver");
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//Connection thadsconn = DriverManager.getConnection("jdbc:mysql://localhost:3306/TravelExperts","root","password");
			Connection thadsconn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","ictoosd","ictoosd");
			thadsconn.setAutoCommit(false);
			thadsstmt = thadsconn.createStatement();
		}catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	protected void thadsAgent()
	{
		try
		{
			//String thadssql = "SELECT * FROM Agents WHERE AgentId=" + jTextField1;
			//thadsresult = (ResultSet) thadsstmt.executeQuery("SELECT * FROM Agents WHERE AgentId=" + jTextField1);
			thadsresult = (ResultSet) thadsstmt.executeQuery("SELECT PPS.PackageId,PS.ProductSupplierId,S.SupName,SC.SupConCompany FROM Packages_Products_Suppliers PPS INNER JOIN Products_Suppliers PS ON PPS.ProductSupplierId = PS.ProductSupplierId INNER JOIN Suppliers S ON PS.SupplierId = S.SupplierId INNER JOIN SupplierContacts SC ON S.SupplierId = SC.SupplierId WHERE PPS.PackageId = 1");
			ResultSetMetaData md = thadsresult.getMetaData();
			int columns = md.getColumnCount();
			if (thadsresult.next())
			{
			
				for(int i = 1; i<=columns; i++)
				{
					columnNames.addElement(md.getColumnName(i));
				}
				
				//get row data
				while(thadsresult.next())
				{
					Vector row = new Vector(columns);
					
					for (int i = 1;i<=columns; i++)
					{
						row.addElement(thadsresult.getObject(i));
					}
					data.addElement(row);
				}
				
				thadsresult.close();
				thadsstmt.close();
				//populate the testfields with selected info
			//	jTextField7.setText(thadsresult.getString(1));
				//jTextField1.setText(thadsresult.getString(2));
				//jTextField6.setText(thadsresult.getString(3));
				//jTextField2.setText(thadsresult.getString(4));
			//	jTextField3.setText(thadsresult.getString(5));
			//	jTextField4.setText(thadsresult.getString(6));
			//	jTextField5.setText(thadsresult.getString(7));
			//	jTextField8.setText(thadsresult.getString(8));
				
			}
			else
			{
				System.out.println("no row selected");
			}
			thadsresult.close();
		}catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	public void thadsCommit()
	{
		//it looks like the transactions are not being committed
		//so this should solve the problem. The commit command is only required when writing to an Oracle back end
		String thadssql ="COMMIT";
		try {
			thadsstmt.executeUpdate(thadssql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void thadsUpdate()
	{
	//	String thadssql = "UPDATE Agents SET "
		//	+ "AgtFirstName='" + jTextField1.getText()
		//	+ "', AgtMiddleInitial='" + jTextField6.getText()
		//	+ "', AgtLastName='" + jTextField2.getText()
		//	+ "', AgtBusPhone='" + jTextField3.getText()
		//	+ "', AgtEmail='" + jTextField4.getText()
		//	+ "', AgtPosition='" + jTextField5.getText()
		//	+ "', AgencyId=" + jTextField8.getText()
			//+ " WHERE AgentId=" + jComboBox1.getSelectedItem();
	int NumberOfRows;
	try
	{
		NumberOfRows = thadsstmt.executeUpdate(thadssql);
		if(NumberOfRows == 0)
		{
			System.out.println("Update Failed");
		}
		else
		{
			System.out.println("updated " + NumberOfRows + " row(s)");
		}

	}catch (SQLException e)
	{
		e.printStackTrace();
	}
	
  }
	
	public void disableAllFields()
	{
		jTextField1.setEnabled(false);
	//	jTextField2.setEnabled(false);
	//	jTextField3.setEnabled(false);
	//	jTextField4.setEnabled(false);
	//	jTextField5.setEnabled(false);
	//	jTextField6.setEnabled(false);
	//	jTextField7.setEnabled(false);
	//	jTextField8.setEnabled(false);
		//enable edit button, disable save button
	//	jButton1.setEnabled(true);
	//	jButton2.setEnabled(false);
		
	}
	
	public void enableAllFields()
	{
		//disable edit button, enable save button
	//	jButton1.setEnabled(false);
	//	jButton2.setEnabled(true);
		jTextField1.setEnabled(true);
	//	jTextField2.setEnabled(true);
	//	jTextField3.setEnabled(true);
	//	jTextField4.setEnabled(true);
	//	jTextField5.setEnabled(true);
	//	jTextField6.setEnabled(true);
	//	jTextField7.setEnabled(false);//id fields do not need to be enabled
	//	jTextField8.setEnabled(false);//id fields do not need to be enabled
	}
	
	public int getRowCount()
	{
		return data.size() / getColumnCount();
	}
	
	
	public int getColumnCount()
	{
		return columnNames.size();
	}
	
	
	public Object getValueAt(int rowIndex, int columnIndex)
	{
		return (String) data.elementAt((rowIndex * getColumnCount())
				+ columnIndex);
	}
	
	public void showData()
	{
		
		//Verify that the data is being extracted properly
		System.out.println(columnNames.get(0));
		System.out.println(columnNames.get(1));
		System.out.println(columnNames.get(2));
		System.out.println(columnNames.get(3));
		//Verify that the data is being extracted properly
		System.out.println(data.get(0));
		System.out.println(data.get(1));
		

	}

}
